﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using CarRental.ViewModel;
using CarRental.Classes;
using CarRental.DB;

namespace CarRental.Database
{
    public abstract class Database
    {
        protected static SqlConnection connection { get; set; }

        public static void Connect()
        {
            connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationDatabase"].ConnectionString);
            connection.Open();
        }

        public static void Disconnect()
        {
            connection.Close();
        }

        #region Cars

        /// <summary>
        /// Pobiera samochody
        /// </summary>
        /// <returns></returns>
        public static List<CarViewModel> GetCars()
        {
            //SqlCommand command = new SqlCommand("SELECT Cars.Id, RegistrationNumber, ProducerName, ModelName, AddDate, ProcuctionYear, IsActive FROM Cars LEFT JOIN Producers ON Cars.ProducerId = Producers.Id LEFT JOIN Models ON Cars.ModelId = Models.Id");
            //command.Connection = connection;
            //SqlDataReader reader = command.ExecuteReader();

            List<CarViewModel> cars = new List<CarViewModel>();

            using (var context = new CarRentalEntities())
            {
                cars = context.Cars.Select(x => new CarViewModel
                {
                    Id = x.Id,
                    ProducerName = x.Model.Producer.ProducerName,
                    ModelName = x.Model.ModelName,
                    RegistrationNumber = x.RegistrationNumber,
                    ProductionYear = x.ProductionYear,
                    AddDate = x.AddDate,
                    IsActive = x.IsActive,
                    Producer = new ProducerViewModel { Id = x.Model.Producer.Id, ProducerName = x.Model.Producer.ProducerName },
                    Model = new ModelViewModel { Id = x.Model.Id, ModelName = x.Model.ModelName }
                }).ToList();
            }

            //while (reader.Read())
            //{
            //    CarViewModel car = new CarViewModel
            //    {
            //        Id = reader.GetInt32(0),
            //        RegistrationNumber = reader.GetString(1),
            //        Producer = reader.GetString(2),
            //        Model = reader.GetString(3),
            //        AddDate = reader.GetDateTime(4),
            //        ProductionYear = reader.GetDateTime(5),
            //        IsActive = reader.GetBoolean(6)
            //    };
            //    cars.Add(car);
            //}

            //reader.Close();
            return cars;
        }

        public static List<CarViewModel> GetCarsPromoted()
        {
            List<CarViewModel> cars = new List<CarViewModel>();

            using (var context = new CarRentalEntities())
            {
                cars = context.Cars.Select(x => new CarViewModel
                {
                    Id = x.Id,
                    ProducerName = x.Model.Producer.ProducerName,
                    ModelName = x.Model.ModelName,
                    RegistrationNumber = x.RegistrationNumber,
                    ProductionYear = x.ProductionYear,
                    AddDate = x.AddDate,
                    IsActive = x.IsActive,
                    Producer = new ProducerViewModel { Id = x.Model.Producer.Id, ProducerName = x.Model.Producer.ProducerName },
                    Model = new ModelViewModel { Id = x.Model.Id, ModelName = x.Model.ModelName }
                }).ToList();

                string folder = System.Configuration.ConfigurationManager.AppSettings["CarsImagesFolder"];

                // - Pobranie zdjęć dla poszczególnych samochodów -
                foreach (var car in cars)
                {
                    car.Images = new List<Image>(context.CarsImages.Where(y => y.CarId == car.Id && y.IsActive).Select(y => new Image
                    {
                        ImageFile = folder + y.ImageFile
                    }).ToArray());
                }
            }

            return cars;
        }

        /// <summary>
        /// Pobiera samochód z bazy
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static CarViewModel GetCar(int Id)
        {
            CarViewModel car = null;

            using (var context = new CarRentalEntities())
            {
                var carFromDb = context.Cars.FirstOrDefault(x => x.Id == Id);

                if (carFromDb != null)
                {
                    car = new CarViewModel
                    {
                        Id = carFromDb.Id,
                        ProducerName = carFromDb.Model.Producer.ProducerName,
                        ModelName = carFromDb.Model.ModelName,
                        RegistrationNumber = carFromDb.RegistrationNumber,
                        ProductionYear = carFromDb.ProductionYear,
                        AddDate = carFromDb.AddDate,
                        IsActive = carFromDb.IsActive,
                        Producer = new ProducerViewModel { Id = carFromDb.Model.Producer.Id, ProducerName = carFromDb.Model.Producer.ProducerName },
                        Model = new ModelViewModel { Id = carFromDb.Model.Id, ModelName = carFromDb.Model.ModelName }
                    };
                }
            }

            return car;
        }

        /// <summary>
        /// Edycja samochodu
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="RegistrationNumber"></param>
        /// <param name="ProductionYear"></param>
        /// <param name="ProducerId"></param>
        /// <param name="ModelId"></param>
        /// <param name="IsActive"></param>
        /// <returns></returns>
        public static bool EditCar(int Id, String RegistrationNumber, DateTime ProductionYear, int ModelId, bool IsActive)
        {
            bool result = false;

            using (var context = new CarRentalEntities())
            {
                var car = context.Cars.FirstOrDefault(x => x.Id == Id);

                if (car != null)
                {
                    car.RegistrationNumber = RegistrationNumber;
                    car.ProductionYear = ProductionYear;
                    car.ModelId = ModelId;
                    car.IsActive = IsActive;

                    context.SaveChanges();

                    result = true;
                }

                return result;
            }
        }


        #endregion Cars

        #region User

        /// <summary>
        /// Pobiera listę wszystkich użytkowników
        /// </summary>
        /// <returns></returns>
        public static List<UserViewModel> GetUsers()
        {
            List<UserViewModel> users = new List<UserViewModel>();

            using (var context = new CarRentalEntities())
            {
                users = context.Users.Select(x => new UserViewModel
                {
                    Id = x.Id,
                    UserName = x.UserName,
                    Gender = x.Gender,
                    IsActive = x.IsActive,
                    RegisterDate = x.RegisterDate,
                    Address = x.Address,
                    PhoneNumber = x.PhoneNumber
                }).ToList();
            }

            return users;
        }

        public static UserViewModel GetUserByUserName(String UserName)
        {
            UserViewModel user = new UserViewModel();

            using (var context = new CarRentalEntities())
            {
                var userFromDB = context.Users.FirstOrDefault(x => x.UserName == UserName);

                if (userFromDB != null)
                {
                    user.Id = userFromDB.Id;
                    user.UserName = userFromDB.UserName;
                    user.Password = userFromDB.Password;
                    user.Gender = userFromDB.Gender;
                    user.IsActive = userFromDB.IsActive;
                    user.RegisterDate = userFromDB.RegisterDate;
                    user.Address = userFromDB.Address;
                    user.PhoneNumber = userFromDB.PhoneNumber;
                }
            }

            return user;
        }

        public static UserViewModel GetUserById(int UserId)
        {
            UserViewModel user = new UserViewModel();

            using (var context = new CarRentalEntities())
            {
                var userFromDB = context.Users.FirstOrDefault(x => x.Id == UserId);

                if (userFromDB != null)
                {
                    user.Id = userFromDB.Id;
                    user.UserName = userFromDB.UserName;
                    user.Password = userFromDB.Password;
                    user.Gender = userFromDB.Gender;
                    user.IsActive = userFromDB.IsActive;
                    user.RegisterDate = userFromDB.RegisterDate;
                    user.Address = userFromDB.Address;
                    user.PhoneNumber = userFromDB.PhoneNumber;
                }
            }

            return user;
        }

        /// <summary>
        /// Edytuje dane użytkownika
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="UserName"></param>
        /// <param name="Email"></param>
        /// <param name="Gender"></param>
        /// <param name="IsActive"></param>
        /// <returns></returns>
        public static bool EditUser(int Id, String UserName, String Gender, String Address, String PhoneNumber, bool IsActive)
        {
            bool result = false;

            using (var context = new CarRentalEntities())
            {
                var user = context.Users.FirstOrDefault(x => x.Id == Id);

                if (user != null)
                {
                    user.UserName = UserName;
                    user.Gender = Gender;
                    user.IsActive = IsActive;
                    user.Address = Address;
                    user.PhoneNumber = PhoneNumber;

                    context.SaveChanges();

                    result = true;
                }

                return result;
            }
        }

        /// <summary>
        /// Usuwa danego użytkownika
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static bool DeleteUser(int Id)
        {
            bool result = false;

            using (var context = new CarRentalEntities())
            {
                var user = context.Users.FirstOrDefault(x => x.Id == Id);

                if (user != null)
                {
                    context.Users.Remove(user);
                    context.SaveChanges();

                    result = true;
                }

                return result;
            }
        }

        /// <summary>
        /// Zapisuje nowego użytkownika
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Email"></param>
        /// <param name="Gender"></param>
        /// <param name="Address"></param>
        /// <param name="PhoneNumber"></param>
        /// <param name="IsActive"></param>
        /// <returns></returns>
        public static bool CreateUser(String UserName, String Gender, String Address, String PhoneNumber, bool IsActive)
        {
            bool result = false;

            using (var context = new CarRentalEntities())
            {
                User newUser = new User();

                newUser.UserName = UserName;
                newUser.Gender = Gender;
                newUser.IsActive = IsActive;
                newUser.Address = Address;
                newUser.PhoneNumber = PhoneNumber;

                context.Users.Add(newUser);
                context.SaveChanges();

                result = true;
            }

            return result;
        }

        #endregion User

        #region Producer

        public static List<ProducerViewModel> GetProducers()
        {
            List<ProducerViewModel> producers = new List<ProducerViewModel>();

            using (var context = new CarRentalEntities())
            {
                producers = context.Producers.Select(x => new ProducerViewModel
                {
                    Id = x.Id,
                    ProducerName = x.ProducerName
                }).ToList();
            }

            return producers;
        }

        #endregion Producer

        #region Model

        /// <summary>
        /// Pobiera modele samochodów
        /// </summary>
        /// <param name="ProducerId">Id konkretnego producenta</param>
        /// <returns></returns>
        public static List<ModelViewModel> GetModels(int? ProducerId = null)
        {
            List<ModelViewModel> models = new List<ModelViewModel>();

            using (var context = new CarRentalEntities())
            {
                var modelsFromDB = ProducerId.HasValue 
                    ? context.Models.Where(x => x.ProducerId == ProducerId.Value) 
                    : context.Models;

                models = modelsFromDB.Select(x => new ModelViewModel
                {
                    Id = x.Id,
                    ModelName = x.ModelName
                }).ToList();
            }

            return models;
        }

        #endregion Model
    }
}
